#encoding=utf8

import cx_Oracle
import os
import web
from web.contrib.template import render_jinja
render = render_jinja('template', encoding='utf-8')


nginx_staticfile_path = "./static/downfile/"

urls=(
        '/req_download', 'download',
        '/req_query', 'query',
        '/test', 'test'
        )

def execsql():
    os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'
    conn = cx_Oracle.connect('test', 'test', '127.0.0.1:1521/orcl')
    cr = conn.cursor()

    sql= open('sql.txt', 'r').read()
    print sql
    cr.execute(sql)
    rowhead = [(x[0], str(x[1])) for x in cr.description]

    rs = cr.fetchall();
    conn.close()
    rowlength = len(rowhead)
    return rowhead, rs, rowlength 

class download:
    def POST(self):
        userdata = web.input()
        userdata.flag
        filename = "test.csv"

        print userdata.flag, type(userdata.flag)
        exportflag = False
        if userdata.flag == '1':
            exportflag = export(filename)
        else:
            pass

        if exportflag != False:
            return render.downfile(locals())
        else:
            return "download failed"

    def GET(self):
        filename="testfile"
        return render.downfile(locals())

def export(filename):

    #get result from db
    rowhead,rs,rowlength = execsql() 
    rowheadname = [x[0] for x in rowhead]

    s = '哈喽'
    t = type(s)

    f = open(nginx_staticfile_path +filename, 'w')
    for column in rowheadname:
        if isinstance(column, t):
            f.write(column.decode('utf8').encode('gbk') + ',')
        else:
            f.write(str(column) + ',')
    f.write('\n')

    for row in rs:
        for x in row:
            if isinstance(x, t):
                f.write(x.decode('utf8').encode('gbk') + ',')
            else:
                f.write(str(x)+',')
            
        f.write('\n')
    f.close()

    return True

class query:
    def GET(self):
        #do db operations
        rowhead, rs, rowlength = execsql()
        print rowhead
        #print rs
        print rowlength

        return render.query(locals())


class test:
    def POST(self):
        teststr = "hello I am teststr from webpy"
        return render.test(locals())
    def GET(self):
        teststr = "hello I am teststr from webpy"
        return render.test(locals())

if __name__ =="__main__":
    app = web.application(urls, globals())
    app.run()




